/**
 * 
 */
package com.lastct.controller;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import javax.naming.NamingException;

import com.lastct.connection.ConnectionUtil;
import com.lastct.controller.interfaces.OfferManager;
import com.lastct.dao.Location;
import com.lastct.dao.Offer;
import com.lastct.dao.OfferTypes;

/**
 * @author Marco DC
 *
 */
public class OfferManagerManagerConcrete implements OfferManager{

	@Override
	public Offer[] getOffers(Integer userID) {
		ArrayList<Offer> result = new ArrayList<Offer>();
		Connection connection = null;
		try {
			connection = ConnectionUtil.getConnectionDB();
			String query = "SELECT   description, short_description, ts_inserimento, description_type, image, data_inizio_prenotazione,  " + 
			"data_fine_prenotazione, data_inizio_offerta, data_fine_offerta, num_min, num_max, " +
			"name, address, longitude, latitude, idoffers, id , num_accepted " +
			"FROM view_offers";
			if(userID != -1){
				query += " where user_id = " + userID.longValue();
			}
			PreparedStatement ps = connection.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
							
				Offer offTmp = new Offer();
				offTmp.setAddress(rs.getString("name"));
				offTmp.setLatitude(rs.getDouble("latitude"));
				offTmp.setDataFineOfferta(rs.getTimestamp("data_fine_offerta"));
				offTmp.setDataInizioOfferta(rs.getTimestamp("data_inizio_offerta"));
				offTmp.setDataInizioPrenotazione(rs.getTimestamp("data_inizio_prenotazione"));
				offTmp.setDataFinePrenotazione(rs.getTimestamp("data_fine_prenotazione"));
				offTmp.setDescription(rs.getString("description"));
				offTmp.setDescriptionType(rs.getString("description_type"));
				offTmp.setId(rs.getInt("id"));
				offTmp.setIdoffers(rs.getInt("idoffers"));
				offTmp.setImage(rs.getString("image"));
				offTmp.setName(rs.getString("name"));
				offTmp.setNumAccepted(rs.getInt("num_accepted"));
				offTmp.setLongitude(rs.getDouble("longitude"));
				offTmp.setNumMax(rs.getInt("num_max"));
				offTmp.setNumMin(rs.getInt("num_min"));
				offTmp.setShortDescription(rs.getString("short_description"));
				offTmp.setTsInserimento(rs.getTimestamp("ts_inserimento"));
				result.add(offTmp);				
			}
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		Offer[] offlist = new Offer[result.size()]; 
				result.toArray(offlist);
		return result.toArray(offlist);
	}
	
	@Override
	public Offer getOffer(Integer offerId) {
		Offer offTmp = new Offer();
		Connection connection = null;
		try {
			connection = ConnectionUtil.getConnectionDB();
			String query = "SELECT description, short_description, ts_inserimento, description_type, image, data_inizio_prenotazione,  " + 
					"data_fine_prenotazione, data_inizio_offerta, data_fine_offerta, num_min, num_max, " +
					"name, address, longitude, latitude, idoffers, id , num_accepted " +
					"FROM view_offers "+
					"where idoffers = ? ";
			
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, offerId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				
				offTmp.setAddress(rs.getString("name"));
				offTmp.setLatitude(rs.getDouble("latitude"));
				offTmp.setDataFineOfferta(rs.getTimestamp("data_fine_offerta"));
				offTmp.setDataInizioOfferta(rs.getTimestamp("data_inizio_offerta"));
				offTmp.setDataInizioPrenotazione(rs.getTimestamp("data_inizio_prenotazione"));
				offTmp.setDataFinePrenotazione(rs.getTimestamp("data_fine_prenotazione"));
				offTmp.setDescription(rs.getString("description"));
				offTmp.setDescriptionType(rs.getString("description_type"));
				offTmp.setId(rs.getInt("id"));
				offTmp.setIdoffers(rs.getInt("idoffers"));
				offTmp.setImage(rs.getString("image"));
				offTmp.setName(rs.getString("name"));
				offTmp.setNumAccepted(rs.getInt("num_accepted"));
				offTmp.setLongitude(rs.getDouble("longitude"));
				offTmp.setNumMax(rs.getInt("num_max"));
				offTmp.setNumMin(rs.getInt("num_min"));
				offTmp.setShortDescription(rs.getString("short_description"));
				offTmp.setTsInserimento(rs.getTimestamp("ts_inserimento"));
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return offTmp;
	}
	
	@Override
	public Offer[] getOffersByType(String type) {
		ArrayList<Offer> result = new ArrayList<Offer>();
//		Connection connection = null;
//		try {
//			connection = ConnectionUtil.getConnectionDB();
//			String query = "SELECT idoffers, name, description, offer, time, image FROM offers where offer_type = ? ";
//			
//			PreparedStatement ps = connection.prepareStatement(query);
//			ps.setString(1, type);
//			ResultSet rs = ps.executeQuery();
//			while (rs.next()) {
//				// Read values using column name
//				int id = rs.getInt("idoffers");
//				String name = rs.getString("name");
//				String description = rs.getString("description");
//				String offer = rs.getString("offer");
//				String image = rs.getString("image");
//				long time = rs.getLong("time");
//				
//				Offer offTmp = new Offer();
//				offTmp.setId(id);
//				offTmp.setName(name);
//				offTmp.description = description;
//				offTmp.image = image;
//				offTmp.time = time;
//				offTmp.offer = offer;
//				result.add(offTmp);				
//			}
//		} catch (NamingException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} finally {
//			try {
//				connection.close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//		}
		Offer[] offlist = new Offer[result.size()]; 
		result.toArray(offlist);
		return result.toArray(offlist);
	}

	
	
	@Override
	public OfferTypes[] getOfferTypes() {
		ArrayList<OfferTypes> result = new ArrayList<OfferTypes>();
		Connection connection = null;
		try {
			connection = ConnectionUtil.getConnectionDB();
			String query = "SELECT id, description, type, image FROM offer_types";
			
			PreparedStatement ps = connection.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				// Read values using column name
				int id = rs.getInt("id");
				String description = rs.getString("description");
				String type = rs.getString("type");
				String image = rs.getString("image");
				
				OfferTypes offTmp = new OfferTypes();
				offTmp.setId(id);
				offTmp.setDescription(description);
				offTmp.setImage(image);
				offTmp.setType(type);
				result.add(offTmp);				
			}
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		OfferTypes[] offlist = new OfferTypes[result.size()]; 
				result.toArray(offlist);
		return result.toArray(offlist);
	}

	@Override
	public boolean insert(Offer newOffer) {
		
		Connection connection = null;
		boolean ok = false;
		try {
			connection = ConnectionUtil.getConnectionDB();
			String query = "insert into offers "+
						"(`location_id`, "+
						 "`description`, "+
						 "`short_description`, "+
						 "`ts_inserimento`, "+
						 "`offer_type`, "+
						 "`data_inizio_prenotazione`, "+
						 "`data_fine_prenotazione`, "+
						 "`data_inizio_offerta`, "+
						 "`data_fine_offerta`, "+
						 "`num_max`, "+
						 "`stato` "+
						") "+
						"value "+
						"(?,?,?,now(),?,?,?,?,?,?,'IN_ATTESA_DI_CONFERMA'); ";
			
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, newOffer.getId());
			ps.setString(2, newOffer.getDescription());
			ps.setString(3, newOffer.getShortDescription());
			ps.setInt(4, 1);
			ps.setTimestamp(5, new Timestamp(newOffer.getDataInizioPrenotazione().getTime()));
			ps.setTimestamp(6, new Timestamp(newOffer.getDataFinePrenotazione().getTime()));
			ps.setTimestamp(7, new Timestamp(newOffer.getDataInizioOfferta().getTime()));
			ps.setTimestamp(8, new Timestamp(newOffer.getDataFineOfferta().getTime()));
			ps.setInt(9, newOffer.getNumMax());
			ok = ps.execute();
			
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ok;
	}

	@Override
	public Location[] getLocationsByIdUser(Integer userId) {
		ArrayList<Location> result = new ArrayList<Location>();
		Connection connection = null;
		try {
			connection = ConnectionUtil.getConnectionDB();
			String query = "SELECT id, "+
						   		  "name, "+
						   		  "address, "+
						   		  "user_id, "+
						   		  "latitude, "+
						   		  "longitude "+
						   "from location "+
						   "where location.user_id = ? ";
			
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, userId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
							
				Location locTmp = new Location();
				locTmp.setId(rs.getInt("id"));
				locTmp.setName(rs.getString("name"));
				locTmp.setAddress(rs.getString("address"));
				locTmp.setUserId(rs.getInt("user_id"));
				locTmp.setLatitude(rs.getDouble("latitude"));
				locTmp.setLongitude(rs.getDouble("longitude"));
				
				result.add(locTmp);				
			}
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		Location[] loclist = new Location[result.size()]; 
				result.toArray(loclist);
		return result.toArray(loclist);
	}
	
}
